package com.navi168.fwq21.db;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.navi168.fwq21.R;
import com.navi168.fwq21.friend.mod.Cheji;
import com.navi168.fwq21.friend.mod.Myfriend;
import com.navi168.fwq21.hyzl.mod.Favo;
import com.navi168.fwq21.hyzl.mod.Xfhistroy;
import com.navi168.fwq21.util.Constants;

import android.R.array;
import android.R.integer;
import android.R.string;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.UrlQuerySanitizer.ValueSanitizer;
import android.util.Log;
import android.widget.SimpleCursorAdapter;

public class Sqlh extends SQLiteOpenHelper {
	private static String DB_PATH = "/data/data/com.navi168.fwq21/databases/";
	private static String DB_NAME = "navi168.db";
	private static Sqlh databaseHelper;
	private static SQLiteDatabase db;
	private Cursor c = null;
	private Cursor c1 =null;

	public Cursor getC1() {
		return c1;
	}

	public void setC1(Cursor c1) {
		this.c1 = c1;
	}

	public Cursor getC() {
		return c;
	}

	public void setC(Cursor c) {
		this.c = c;
	}

	private Context context;

	private Sqlh(Context context) {
		super(context, DB_NAME, null, 1);
		this.context = context;
	}

	public static Sqlh getInstance(Context context) {
		if (databaseHelper == null) {
			databaseHelper = new Sqlh(context);
			databaseHelper.openDataBase();

			if (db == null) {
				try {
					db = databaseHelper.getWritableDatabase();
					databaseHelper.copyDatabase();
				} catch (Exception e) {
					Log.d("mylog", "Error in database creation");
				}

				databaseHelper.openDataBase();
			}
		}
		return databaseHelper;
	}

	private void copyDatabase() throws IOException {
		InputStream is = context.getResources().openRawResource(R.raw.navi168);
		OutputStream os = new FileOutputStream(DB_PATH + DB_NAME);
		byte[] buffer = new byte[1024];
		int length;
		while ((length = is.read(buffer)) > 0) {
			os.write(buffer, 0, length);
		}

		os.flush();
		os.close();
		is.close();
	}

	private void openDataBase() {
		try {
			db = SQLiteDatabase.openDatabase(DB_PATH + DB_NAME, null,
					SQLiteDatabase.OPEN_READWRITE
							| SQLiteDatabase.NO_LOCALIZED_COLLATORS);
		} catch (SQLiteException e) {
			// database does't exist yet
		}
	}

	@Override
	public synchronized void close() {
		if (db != null) {
			db.close();
		}
		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	public long insert(ContentValues values, String table) {

		return db.insert(table, null, values);

	}

	public Cursor query(String table) {

		Cursor c = db.query(table, null, null, null, null, null, null);
		c.close();
		return c;
	}

	public int del(int id, String table) {

		int num = db.delete(table, "id=?", new String[] { String.valueOf(id) });
		if (c != null) {
			c.requery();
		}

		return num;
	}

	public int del(String mob) {

		int num = db.delete("cheji", "mobile = ?", new String[] { mob });
		if (c != null) {
			c.requery();
		}

		return num;
	}

	public void delall(String table) {

		db.delete(table, "id>?", new String[] { String.valueOf(0) });

	}

	public SimpleCursorAdapter getProvince(Context context) {
		SimpleCursorAdapter list = null;
		c = db.rawQuery(
				"select ProvinceCode as _id, ProvinceName from Province", null);
		if (c.getCount() > 0) {
			list = new SimpleCursorAdapter(context,
					android.R.layout.simple_spinner_item, c,
					new String[] { "ProvinceName" },
					new int[] { android.R.id.text1 });

		}
		list.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
		return list;

	}

	public SimpleCursorAdapter getCity(Context context, String id) {
		SimpleCursorAdapter list = null;
		c = db.rawQuery(
				"select CityID as _id, CityName from City where ProvinceCode=? ",
				new String[] { id });
		if (c.getCount() > 0) {
			list = new SimpleCursorAdapter(context,
					android.R.layout.simple_spinner_item, c,
					new String[] { "CityName" },
					new int[] { android.R.id.text1 });

		}
		list.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
		return list;
	}

	public String getCityName(long id) {
		String citynameString = "";
		Cursor c = db.rawQuery(
				"select CityID as _id, CityName from City where CityID=? ",
				new String[] { String.valueOf(id) });

		while (c.moveToFirst()) {
			citynameString = c.getString(1);
			break;
		}

		c.close();
		return citynameString;

	}

	public int add_Newmyfriend(Myfriend myfriend) {
		ContentValues values = new ContentValues();
		values.put("friend_nike", myfriend.getFriendname());
		values.put("friend_state", myfriend.getFriendstate());
		values.put("friend_mob_number", myfriend.getFriendmob());
		values.put("friend_joindate", myfriend.getJoindate());
		values.put("friend_car_number", myfriend.getFriendcar());
		values.put("friend_email", myfriend.getFriendemail());
		values.put("friend_adress", myfriend.getAdress());
		values.put("friend_adress_lat", myfriend.getAlat());
		values.put("friend_adress_long", myfriend.getAlong());
		Long numLong = db.insert("friend", "", values);
		return numLong.intValue();

	}

	public int updateMyfriend(Myfriend myfriend) {
		ContentValues values = new ContentValues();
		values.put("friend_nike", myfriend.getFriendname());
		values.put("friend_mob_number", myfriend.getFriendmob());
		values.put("friend_car_number", myfriend.getFriendcar());
		values.put("friend_email", myfriend.getFriendemail());
		values.put("friend_adress", myfriend.getAdress());
		int a = 0;

		a = db.update("friend", values, "friend_id = ?",
				new String[] { String.valueOf(myfriend.getFriendid()) });
		Log.i("mylog",
				"a" + a + "myfriend.getFriendid():" + myfriend.getFriendid());

		return a;
	}

	public ArrayList<Myfriend> getMyfriends() {
		ArrayList<Myfriend> mys = new ArrayList<Myfriend>();
		Cursor c = db.query("friend", null, null, null, null, null, null);
		c.moveToFirst();
		while (!c.isAfterLast() && (c.getString(1) != null)) {
			Myfriend myfriend = new Myfriend();
			myfriend.setAdress(c.getString(7));
			myfriend.setAlat(c.getInt(8));
			myfriend.setAlong(c.getInt(7));
			myfriend.setFriendcar(c.getString(5));
			myfriend.setFriendemail(c.getString(6));
			myfriend.setFriendmob(c.getString(3));
			Log.i("mylog", c.getString(1) + "<--");
			myfriend.setFriendname(c.getString(1));
			myfriend.setFriendstate(c.getString(2));
			myfriend.setFriendid(c.getInt(0));
			mys.add(myfriend);
			c.moveToNext();
		}
		// c.close();
		Log.i("mylog", mys.size() + "@@");
		return mys;
	}

	public int saveHis(Xfhistroy xfh) {
		ContentValues values = new ContentValues();
		values.put("histroy_flag", xfh.getHistroy_flag());
		values.put("histroy_fy", xfh.getHistroy_fy());
		values.put("histroy_jondate", xfh.getHistroy_jondate());
		values.put("histroy_number", xfh.getHistroy_number());
		values.put("histroy_person", xfh.getHistroy_person());
		values.put("histroy_snippet", xfh.getHistroy_snippet());
		values.put("histroy_title", xfh.getHistroy_title());
		values.put("histroy_lat", xfh.getHistroy_lat());
		values.put("histroy_long", xfh.getHistroy_long());
		Long numLong = db.insert("histroylist", null, values);
		return numLong.intValue();

	}

	public int add(Favo favo) {
		ContentValues values = new ContentValues();
		values.put("fav_snippet", favo.getFav_snipeet());
		values.put("fav_title", favo.getFav_title());
		values.put("Join_date", favo.getJoin_date());
		values.put("fav_lat", favo.getFav_lat());
		values.put("fav_lon", favo.getFav_lon());
		int numLong = (int) db.insert("fav_list", null, values);
		return numLong;

	}

	public ArrayList<Favo> getFavcursor() {
		ArrayList<Favo> fl = new ArrayList<Favo>();
		c = db.rawQuery("select * from fav_list order by join_date desc", null);
		c.moveToFirst();
		while (!c.isAfterLast()) {
			Favo favo = new Favo();
			favo.setId(c.getInt(0));
			favo.setFav_title(c.getString(1));
			favo.setFav_snipeet(c.getString(2));
			favo.setFav_lat(c.getInt(4));
			favo.setFav_lon(c.getInt(3));
			fl.add(favo);
			c.moveToNext();
			Log.i("mylog", "here 5");
		}
		return fl;
	}

	public ArrayList<Xfhistroy> getxfhList() {
		ArrayList<Xfhistroy> xfhs = new ArrayList<Xfhistroy>();
		c = db.rawQuery(
				"select * from histroylist where histroy_flag = ? order by histroy_jondate desc",
				new String[] { "1" });
		c.moveToFirst();
		while (!c.isAfterLast()) {
			Xfhistroy xfh = new Xfhistroy();
			xfh.setHistroy_id(c.getInt(0));
			xfh.setHistroy_title(c.getString(1));
			xfh.setHistroy_snippet(c.getString(2));
			xfh.setHistroy_lat(c.getInt(3));
			xfh.setHistroy_long(c.getInt(4));
			xfh.setHistroy_flag(c.getString(5));
			xfh.setHistroy_number(c.getString(6));
			xfh.setHistroy_jondate(c.getString(7));
			xfh.setHistroy_person(c.getString(8));
			xfh.setHistroy_fy(c.getString(9));
			xfhs.add(xfh);
			c.moveToNext();
		}
		c.close();
		return xfhs;
	}

	public ArrayList<Cheji> getchejihao(String mob) {
		ArrayList<Cheji> chejis = new ArrayList<Cheji>();
		c = db.rawQuery("select * from cheji where mobile = ?",
				new String[] { mob });
		c.moveToFirst();
		while (!c.isAfterLast()) {
			Cheji cheji = new Cheji();
			cheji.setId(c.getInt(0));
			cheji.setMobile(c.getString(1));
			cheji.setCheji(c.getString(2));
			chejis.add(cheji);
			c.moveToNext();
		}
		c.close();
		return chejis;
	}

	public int saveChejis(ArrayList<Cheji> chejis) {
		int num = 0;
		for (Cheji cj : chejis) {
			ContentValues values = new ContentValues();
			values.put("mobile", cj.getMobile());
			values.put("cheji", cj.getCheji());
			num += db.insert("cheji", null, values);
		}
		return num;
	}

	public int updateChejis(ArrayList<Cheji> chejis) {
		int num = 0;
		for (Cheji cj : chejis) {
			ContentValues values = new ContentValues();
			values.put("mobile", cj.getMobile());
			values.put("cheji", cj.getCheji());
			Log.i("mylog", "cheji & id:" + cj.getCheji() + "&" + cj.getId());
			num += db.update("cheji", values, "id = ?",
					new String[] { String.valueOf(cj.getId()) });
		}
		return num;
	}

	public String getAllMob() {
		String mobs = "";
		c = db.rawQuery("select friend_mob_number from friend", null);
		c.moveToFirst();
		while (!c.isAfterLast()) {
			String mobString = c.getString(0);
			if (mobString.length() < 11) {
				c.moveToNext();
				continue;
			}
			mobs += mobString + "|";
			c.moveToNext();
		}

		System.out.println(mobs.substring(0, mobs.length() - 1));
		return mobs.substring(0, mobs.length() - 1);

	}

	public String getAllCar() {
		String mobs = "";
		
		c = db.rawQuery("select friend_mob_number from friend", null);
		c.moveToFirst();
		while (!c.isAfterLast()) {
			String mobString = c.getString(0);
			if (mobString.length() < 11) {
				c.moveToNext();
				continue;
			}
			
			c1 = db.rawQuery("select cheji from cheji where mobile = ?",
					new String[] { mobString });

			c1.moveToFirst();
			String cars = "";
			while (!c1.isAfterLast()) {
				String carsString = c1.getString(0);
				cars += carsString + ",";
				c1.moveToNext();
				
			}
			if (cars!="") {
				mobs += mobString +"|" + cars.substring(0, cars.length()-1)+"||";
				cars = "";
			}
			Log.i(Constants.TAG, mobs);
			c.moveToNext();

		}
		System.out.println("mobs : "+mobs);
		return mobs.substring(0,mobs.length()-2);
	}

}
